﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using InfoLayer;

namespace DataAccessLayer
{
    public class ChiTietBaoCaoMatDoDataAccess : DataAccessAbstract
    {
        private OleDbConnection _cnn;
        private OleDbDataAdapter _da;
        private OleDbCommand _cmd;
        private OleDbDataReader _dr;
        private String _sqlString;

        public ArrayList LayDanhSach()
        {
            ArrayList kq = new ArrayList();
            ChiTietBaoCaoMatDoInfo Info;
            _sqlString = "Select * from CHITIETBAOCAOMATDO";
            //khoi tao bien ket noi CSDL
            _cnn = ConnectionData();
            //chuoi ket noi CSDL
            _cmd = new OleDbCommand(_sqlString, _cnn);
            _dr = _cmd.ExecuteReader();
            //doc du lieu tu CSDL
            while (_dr.Read())
            {
                Info = new ChiTietBaoCaoMatDoInfo();
                try
                {
                    //Info.TinhTrangPhong = Convert.ToBoolean(_dr["TinhTrangPhong"].ToString());
                }
                catch (System.Exception ex)
                {
                    _cnn.Close();
                    throw new Exception("Lỗi kết nối dữ liệu.");
                }
                kq.Add(Info);
            }
            _cnn.Close();
            return kq;
        }

        public DataTable LayBangDanhSachChiTietBaoCao()
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select * from CHITIETBAOCAOMATDO";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public void Insert(ref ChiTietBaoCaoMatDoInfo chiTietBaoCaoMatDoInfo)
        {
            //viet cau lenh SQL
            _sqlString = "INSERT INTO CHITIETBAOCAOMATDO (MaChiTietBaoCaoMatDo, MaBaoCaoMatDo, SoNgayThue, TyLe, MaPhong) VALUES ('" +
                chiTietBaoCaoMatDoInfo.MaChiTietBaoCaoMatDo + "','" +
                chiTietBaoCaoMatDoInfo.MaBaoCaoMatDo + "'," +
                chiTietBaoCaoMatDoInfo.SoNgayThue + "," +
                chiTietBaoCaoMatDoInfo.TyLe + ",'" +
                chiTietBaoCaoMatDoInfo.MaPhong + "')";
            //ket noi CSDL
            _cnn = ConnectionData();
            try
            {
                //thu thi cau truy van SQL
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi kết nối cơ sở dữ liệu.");
            }
            _cnn.Close();
        }

        public void Update(ChiTietBaoCaoMatDoInfo chiTietBaoCaoMatDoInfo)
        {
            _sqlString = "UPDATE CHITIETBAOCAOMATDO SET " +
                                "MaBaoCaoMatDo = '" + chiTietBaoCaoMatDoInfo.MaBaoCaoMatDo + "'," +
                                "SoNgayThue = " + chiTietBaoCaoMatDoInfo.SoNgayThue + "," +
                                "TyLe = " + chiTietBaoCaoMatDoInfo.TyLe + "," +
                                "MaPhong = '" + chiTietBaoCaoMatDoInfo.MaPhong + "'" +
                                "WHERE MaChiTietBaoCaoMatDo = '" + chiTietBaoCaoMatDoInfo.MaChiTietBaoCaoMatDo + "'";
            _cnn = ConnectionData();
            try
            {
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi Update dữ liệu.");
            }
            _cnn.Close();
        }

        public void Delete(ChiTietBaoCaoMatDoInfo chiTietBaoCaoMatDoInfo)
        {
            _sqlString = "delete from CHITIETBAOCAOMATDO where MaChiTietBaoCaoMatDo = '" + chiTietBaoCaoMatDoInfo.MaChiTietBaoCaoMatDo + "'";
            _cnn = ConnectionData();
            try
            {
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi xóa dữ liệu.");
            }
            _cnn.Close();
        }

        public string TaoMaTuDong(string maGoc, int batDau)
        {
            string str, kq = "";
            int so;
            try
            {
                str = maGoc.Substring(batDau);
                kq = maGoc.Substring(0, batDau);
                so = Convert.ToInt32(str) + 1;
                for (int i = 0; i < str.Length - so.ToString().Length; i++)
                    kq += "0";
                kq = kq + so.ToString();
                return kq;
            }
            catch //(System.Exception ex)
            {
                return null;
            }
        }
    }
}
